package com.dshore.messagecenter.dao.warning;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.dshore.messagecenter.model.warning.MrWarningPerson;
@Repository
public class WarningPersonDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	/**
	 * 总记录数
	 * @param warningPerson
	 * @return
	 */
	public long queryCountByPrama(MrWarningPerson warningPerson) {
		String principal = warningPerson.getPrincipal();
		String loginAccount=warningPerson.getLoginAccount();
		List<Object> list=new ArrayList<>();
		StringBuffer sql=new StringBuffer();
		 sql.append("select count(1) from T_MR_WARNING_PERSON where Del_FLAG=0 ");
		 if(StringUtils.isNotBlank(principal)) {
			 sql.append("and PRINCIPAL like ?");
			 list.add("%"+principal+"%");
		 }
		 if(!"请选择".equals(loginAccount)) {
			 sql.append("and LOGIN_ACCOUNT = ?");
			 list.add(loginAccount);
		 }
		 Object[]args=list.toArray();
		return jdbcTemplate.queryForObject(sql.toString(),args,long.class);
	}
	
	/**
	 * 查询预警人所有信息
	 * @param warningPerson
	 * @return
	 */
	public List<MrWarningPerson> queryListByPrama(MrWarningPerson warningPerson) {
		String principal = warningPerson.getPrincipal();
		String loginAccount=warningPerson.getLoginAccount();
		List<Object> list=new ArrayList<>();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from (");
		sql.append("select A.*, rownum rn from (select * from T_MR_WARNING_PERSON  where del_flag = 0");		
		if(StringUtils.isNotBlank(principal)) {
			sql.append("and PRINCIPAL like ?");
			 list.add("%"+principal+"%");
		}
		 if(!"请选择".equals(loginAccount)) {
			 sql.append("and LOGIN_ACCOUNT = ?");
			 list.add(loginAccount);
		 }
		sql.append(" ) A   )");
		sql.append(" where rn between ? and ? ");
		sql.append(" order by created_time desc ");
		list.add(warningPerson.getOffset()+1);
		list.add(warningPerson.getOffset()+warningPerson.getLimit());
		Object[]args=list.toArray();
		RowMapper<MrWarningPerson> warningPersonRowMapper = new BeanPropertyRowMapper<>(MrWarningPerson.class);
		return jdbcTemplate.query(sql.toString(),args, warningPersonRowMapper);
	}
	/**
	 * 获取单个信息
	 * @param id
	 * @return
	 */
	public MrWarningPerson getById(String id) {
		String sql="select * from T_MR_WARNING_PERSON where id=?";
		RowMapper<MrWarningPerson> mrWarningPersonRowMapper = new BeanPropertyRowMapper<>(MrWarningPerson.class);
		return jdbcTemplate.queryForObject(sql,mrWarningPersonRowMapper,id);
	}
	/**
	 * 新增
	 * @param warningPerson
	 * @return
	 */
	public Integer addWarningPerson(MrWarningPerson warningPerson) {
		String sql ="insert into T_MR_WARNING_PERSON(id,PRINCIPAL,"
				+ "PRINCIPAL_CONTACT,PRINCIPAL_EMAIL,LOGIN_ACCOUNT,CREATED_TIME,DEL_FLAG) values (?, ?, ?, ?,?,?,?)";
		return jdbcTemplate.update(sql,warningPerson.getId(),warningPerson.getPrincipal(),
				warningPerson.getPrincipalContact(),warningPerson.getPrincipalEmail(),
				warningPerson.getLoginAccount(),new Date(),0);
	}
	/**
	 * 修改
	 * @param warningPerson
	 * @return
	 */
	public Integer changeWarningPerson(MrWarningPerson warningPerson) {
		String sql="update T_MR_WARNING_PERSON set PRINCIPAL=?,PRINCIPAL_CONTACT=?,PRINCIPAL_EMAIL=?"
				+ ",LOGIN_ACCOUNT=?,MODIFIED_TIME=? where id = ?";
		return jdbcTemplate.update(sql,warningPerson.getPrincipal(),warningPerson.getPrincipalContact(),
				warningPerson.getPrincipalEmail(),warningPerson.getLoginAccount(),new Date(),warningPerson.getId());
	}
	/**
	 * 删除
	 * @param idArr
	 * @return
	 */
	public void deleteByIds(String[] idArr) {
		String sql="update T_MR_WARNING_PERSON set del_flag=1,modified_time=?  where id =?";	
		 jdbcTemplate.batchUpdate(sql,new BatchPreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
				ps.setString(2, idArr[i].toString());
			}			
			@Override
			public int getBatchSize() {
				return idArr.length;
			}
		});
	}
	/**
	 * 删除前检查监控阈值里面值是否关联
	 * @param idArr
	 * @return
	 */
	public List<Map<String, Object>> selectWarningThresholds(String[] ids) {
		StringBuffer sql = new StringBuffer("select * from T_MR_WARNING_THRESHOLD where DEL_FLAG=0 and PRINCIPAL_ID in (");
		for(int i=0;i<ids.length;i++) {
			sql.append(" ? ");
			if(i!=ids.length-1) {
				sql.append(" , ");
			}
		}
		sql.append(" ) ");
		Object[] param=ids;
		return jdbcTemplate.queryForList(sql.toString(),param);
		
		
	}
}
